package com.school.dao;

import com.school.entity.Building;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * 楼栋DAO
 */
@Repository
public class BuildingDao {
    
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    
    /**
     * 插入楼栋
     */
    public Long insert(Building building) {
        String sql = "INSERT INTO tb_building (building_name, floors, total_rooms, capacity, occupied, theme_color, remark) " +
                "VALUES (:buildingName, :floors, :totalRooms, :capacity, :occupied, :themeColor, :remark)";
        
        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("buildingName", building.getBuildingName())
                .addValue("floors", building.getFloors())
                .addValue("totalRooms", building.getTotalRooms())
                .addValue("capacity", building.getCapacity())
                .addValue("occupied", building.getOccupied())
                .addValue("themeColor", building.getThemeColor())
                .addValue("remark", building.getRemark());
        
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql, params, keyHolder);
        return keyHolder.getKey().longValue();
    }
    
    /**
     * 更新楼栋
     */
    public int update(Building building) {
        String sql = "UPDATE tb_building SET floors = :floors, total_rooms = :totalRooms, capacity = :capacity, " +
                "occupied = :occupied, theme_color = :themeColor, remark = :remark WHERE id = :id";
        
        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("id", building.getId())
                .addValue("floors", building.getFloors())
                .addValue("totalRooms", building.getTotalRooms())
                .addValue("capacity", building.getCapacity())
                .addValue("occupied", building.getOccupied())
                .addValue("themeColor", building.getThemeColor())
                .addValue("remark", building.getRemark());
        
        return namedParameterJdbcTemplate.update(sql, params);
    }
    
    /**
     * 删除楼栋
     */
    public int deleteById(Long id) {
        String sql = "DELETE FROM tb_building WHERE id = :id";
        MapSqlParameterSource params = new MapSqlParameterSource("id", id);
        return namedParameterJdbcTemplate.update(sql, params);
    }
    
    /**
     * 批量删除楼栋
     */
    public int batchDelete(List<Long> ids) {
        String sql = "DELETE FROM tb_building WHERE id IN (:ids)";
        MapSqlParameterSource params = new MapSqlParameterSource("ids", ids);
        return namedParameterJdbcTemplate.update(sql, params);
    }
    
    /**
     * 根据ID查询
     */
    public Building findById(Long id) {
        String sql = "SELECT * FROM tb_building WHERE id = :id";
        MapSqlParameterSource params = new MapSqlParameterSource("id", id);
        List<Building> list = namedParameterJdbcTemplate.query(sql, params, 
                (rs, rowNum) -> {
                    Building b = new Building();
                    b.setId(rs.getLong("id"));
                    b.setBuildingName(rs.getString("building_name"));
                    b.setFloors(rs.getInt("floors"));
                    b.setTotalRooms(rs.getInt("total_rooms"));
                    b.setCapacity(rs.getInt("capacity"));
                    b.setOccupied(rs.getInt("occupied"));
                    b.setThemeColor(rs.getString("theme_color"));
                    b.setRemark(rs.getString("remark"));
                    b.setCreateTime(rs.getTimestamp("create_time"));
                    b.setUpdateTime(rs.getTimestamp("update_time"));
                    return b;
                });
        return list.isEmpty() ? null : list.get(0);
    }
    
    /**
     * 根据楼栋名称查询
     */
    public Building findByBuildingName(String buildingName) {
        String sql = "SELECT * FROM tb_building WHERE building_name = :buildingName";
        MapSqlParameterSource params = new MapSqlParameterSource("buildingName", buildingName);
        List<Building> list = namedParameterJdbcTemplate.query(sql, params, 
                (rs, rowNum) -> {
                    Building b = new Building();
                    b.setId(rs.getLong("id"));
                    b.setBuildingName(rs.getString("building_name"));
                    b.setFloors(rs.getInt("floors"));
                    b.setTotalRooms(rs.getInt("total_rooms"));
                    b.setCapacity(rs.getInt("capacity"));
                    b.setOccupied(rs.getInt("occupied"));
                    b.setThemeColor(rs.getString("theme_color"));
                    b.setRemark(rs.getString("remark"));
                    b.setCreateTime(rs.getTimestamp("create_time"));
                    b.setUpdateTime(rs.getTimestamp("update_time"));
                    return b;
                });
        return list.isEmpty() ? null : list.get(0);
    }
    
    /**
     * 查询所有楼栋
     */
    public List<Building> findAll() {
        String sql = "SELECT * FROM tb_building ORDER BY building_name";
        return namedParameterJdbcTemplate.query(sql, 
                (rs, rowNum) -> {
                    Building b = new Building();
                    b.setId(rs.getLong("id"));
                    b.setBuildingName(rs.getString("building_name"));
                    b.setFloors(rs.getInt("floors"));
                    b.setTotalRooms(rs.getInt("total_rooms"));
                    b.setCapacity(rs.getInt("capacity"));
                    b.setOccupied(rs.getInt("occupied"));
                    b.setThemeColor(rs.getString("theme_color"));
                    b.setRemark(rs.getString("remark"));
                    b.setCreateTime(rs.getTimestamp("create_time"));
                    b.setUpdateTime(rs.getTimestamp("update_time"));
                    return b;
                });
    }
    
    /**
     * 分页查询
     */
    public List<Building> findByPage(Map<String, Object> params) {
        StringBuilder sql = new StringBuilder("SELECT * FROM tb_building WHERE 1=1");
        
        if (params.get("buildingName") != null && !"".equals(params.get("buildingName"))) {
            sql.append(" AND building_name LIKE CONCAT('%', :buildingName, '%')");
        }
        
        sql.append(" ORDER BY building_name LIMIT :limit OFFSET :offset");
        
        return namedParameterJdbcTemplate.query(sql.toString(), params, 
                (rs, rowNum) -> {
                    Building b = new Building();
                    b.setId(rs.getLong("id"));
                    b.setBuildingName(rs.getString("building_name"));
                    b.setFloors(rs.getInt("floors"));
                    b.setTotalRooms(rs.getInt("total_rooms"));
                    b.setCapacity(rs.getInt("capacity"));
                    b.setOccupied(rs.getInt("occupied"));
                    b.setThemeColor(rs.getString("theme_color"));
                    b.setRemark(rs.getString("remark"));
                    b.setCreateTime(rs.getTimestamp("create_time"));
                    b.setUpdateTime(rs.getTimestamp("update_time"));
                    return b;
                });
    }
    
    /**
     * 统计总数
     */
    public long count(Map<String, Object> params) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM tb_building WHERE 1=1");
        
        if (params.get("buildingName") != null && !"".equals(params.get("buildingName"))) {
            sql.append(" AND building_name LIKE CONCAT('%', :buildingName, '%')");
        }
        
        Long count = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Long.class);
        return count != null ? count : 0L;
    }
}


